import sqlite3
import threading
import logging

class DbSession:
    def __init__(self,dbConn):
        self.dbConn = dbConn
        self.conn = dbConn.conn
        self.lock = dbConn.lock

    def findList(self,sql:str,args:tuple=()) -> tuple:
        '''查询列表'''
        cursor = self.conn.cursor()
        cursor.execute(sql,args)
        items = cursor.fetchall()
        cursor.close()
        dictArr=[]
        for item in items:
            if len(item) == 1:
                dictArr.append(item[0])
            else:
                dictArr.append(item)
        return dictArr

    def findList2(self,sql:str,args:tuple=()) -> dict:
        cursor = self.conn.cursor()
        cursor.execute(sql,args)
        items = cursor.fetchall()
        col = cursor.description
        cursor.close()
        jsonarr=[]
        for row in items:
            jsondata = {}
            for i in range(len(col)):
                jsondata[col[i][0]] = row[i]
            jsonarr.append(jsondata)
        return jsonarr

    def findOne(self,sql:str,args:tuple=()) -> tuple:
        '''查询记录'''
        cursor = self.conn.cursor()
        cursor.execute(sql,args)
        item = cursor.fetchone()
#        logging.debug("["+("0" if item==None else str(len(item)))+"] "+ str(cursor._executed))
        cursor.close()
        if item!=None and len(item) == 1:
            return item[0]
        return item


    def findOne2(self, sql: str, args: tuple = ()) -> dict:
        cursor = self.conn.cursor()
        cursor.execute(sql,args)
        item = cursor.fetchone()
        col = cursor.description
        cursor.close()
        if item!= None:
            jsondata = {}
            for i in range(len(col)):
                jsondata[col[i][0]] = item[i]
            return jsondata
        return None

    def execute(self,sql:str,args:tuple=()):
        '''执行写操作'''
        cursor = self.conn.cursor()
        cursor.execute(sql,args)
#        self.conn.commit()
        item = cursor.fetchone()
        cursor.close()
        return item

    def insertEntity(self,table,entity:dict):
        ''' 插入dict，会自动补充id '''
        sqlArr = []
        fieldArr = []
        placeholderArr = []
        valueArr = []
        for k,v in entity.items():
            fieldArr.append(k)
            valueArr.append(v)
            placeholderArr.append("?")
        
        sqlArr.append("insert into ")
        sqlArr.append(table)
        sqlArr.append(" (")
        sqlArr.append(",".join(fieldArr))
        sqlArr.append(") values (")
        sqlArr.append(",".join(placeholderArr))
        sqlArr.append(")")
        sql = "".join(sqlArr)

        cursor = self.conn.cursor()
        cursor.execute(sql,tuple(valueArr))
#        self.conn.commit()
        if id not in entity:
            entity["id"] = cursor.lastrowid
        cursor.close()
        return entity

    def insertEntityBatch(self,table,entityList:dict):
        ''' 批量插入dict，不会！ 自动补充id '''
        if len(entityList) == 0:
            return
        
        entityFirst = entityList[0]

        sqlArr = []
        fieldArr = []
        placeholderArr = []
        
        for k,v in entityFirst.items():
            fieldArr.append(k)
            placeholderArr.append("?")
        
        sqlArr.append("insert into ")
        sqlArr.append(table)
        sqlArr.append(" (")
        sqlArr.append(",".join(fieldArr))
        sqlArr.append(") values (")
        sqlArr.append(",".join(placeholderArr))
        sqlArr.append(")")
        sql = "".join(sqlArr)

        valuesArr=[]
        for entity in entityList :
            valuesArr.append(tuple(entity.values()))

        cursor = self.conn.cursor()
        cursor.executemany(sql,tuple(valuesArr))
#        self.conn.commit()
        cursor.close()


    def deleteById(self,table,id):
        if id != None :
            sql = "delete from "+table+" where id =? " 
            self.execute(sql,(id,))

    def deleteByIds(self,table,ids):
        if len(ids) > 0 :
            placeholderArr = []
            for id in ids:
                placeholderArr.append("?")
            sql = "delete from "+table+" where id in ("+",".join(placeholderArr)+")"
            self.execute(sql,tuple(ids))


    def updateById(self,table,id,entity:dict):
        if id != None :
            sqlArr = []
            fieldArr = []
            valueArr = []
            for k,v in entity.items():
                fieldArr.append(k+"=?")
                valueArr.append(v)
            valueArr.append(id)

            sqlArr.append("update ")
            sqlArr.append(table)
            sqlArr.append(" set ")
            sqlArr.append(",".join(fieldArr))
            sqlArr.append(" where id=? ")
            sql = "".join(sqlArr)

            cursor = self.conn.cursor()
            cursor.execute(sql,tuple(valueArr))
            cursor.close()

    def updateEntity(self,table,entity:dict):
        id = entity["id"]
        if id != None :
            entityChange = entity.copy()
            del entityChange["id"]
            self.updateById(table,id,entityChange)


class DbConn:
    '''
    数据库底层操作类
    '''
    def __init__(self,db="test.db"):
        self.lock=threading.Lock() 
        self.local=threading.local() 
        self.conn = sqlite3.connect(db,check_same_thread = False)
#        self.conn.autocommit(True)

    def __enter__(self):
#        logging.debug("%s 进入conn %s" % (threading.currentThread().getName(),("0" if not hasattr(self.local,"sessionNum") else self.local.sessionNum)))
        if not hasattr(self.local,"sessionNum")  :
            self.local.sessionNum = 0
        if self.local.sessionNum == 0  :
            self.lock.acquire()
            session = DbSession(self)
            self.local.session = session
            self.local.sessionNum = self.local.sessionNum + 1
        return self.local.session

    def __exit__(self, type, value, trace):
#        logging.debug("%s 离开conn %s" % (threading.currentThread().getName(),("0" if not hasattr(self.local,"sessionNum") else self.local.sessionNum)))
        if self.local.sessionNum == 1 :
            self.local.sessionNum = 0
            self.local.session=None
            if isinstance(value,Exception):
                self.conn.rollback()
            else:
                self.conn.commit()
            self.lock.release()

